package net.sxnic.item.mdata.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.stereotype.Repository;

import net.sxinfo.core.dao.Page;
import net.sxinfo.core.spring25.HibernateDao;
import net.sxnic.item.ItemConstants;
import net.sxnic.item.mdata.Mdata;
import net.sxnic.item.stat.Stat1Bean;

@Repository("mdataDao")
public class HibernateMdataDao extends HibernateDao<Mdata, String> implements MdataDao{

	@Override
	public int deleteSubTables(String mdataId) {
		
		String sql = "";
		
		for(String tn:ItemConstants.SUBTABLES){
			sql = "delete from "+tn+" where baseinfoId=?";
			this.batchExecute(sql, new Object[]{mdataId});
		}
		
		return 0;
	}
	
	public Page getStatDataByName(int pageNo, int pageSize, String zxName){
		
		StringBuffer sb = new StringBuffer();
		
		Page page = null;
		
		sb.append(" select md.id as id, md.appName as zxName, staff.string6 as str1, invest.string9 as str2, ");
		
		sb.append(" achieve.string1 as str3, achieve.string7 as str4, achieve.string31 as str5, achieve.string36 as str6, ");
		
		sb.append(" achieve.string42 as str7, engineer.string13 as str8, engineer.string28 as str9, ar.string1 as str10,  ");
		
		sb.append(" benefit.string6 as str11, openservice.string1 as str12, openservice.string5 as str13 ");
		
		sb.append(" from item_maindata as md ");
		
		sb.append(" left join gczx_staff as staff on md.id = staff.baseinfoId ");
		
		sb.append(" left join gczx_invest as invest on md.id = invest.baseinfoId ");
		
		sb.append(" left join gczx_achievement as achieve on md.id = achieve.baseinfoId ");
		
		sb.append(" left join gczx_benefit as benefit on md.id = benefit.baseinfoId ");
		
		sb.append(" left join gczx_openservice as openservice on md.id = openservice.baseinfoId ");
		
		sb.append(" left join gczx_engineer as engineer on md.id = engineer.baseinfoId ");
		
		sb.append(" left join gczx_achievement_radiative as ar on md.id = ar.baseinfoId ");
		
		if(StringUtils.isNotBlank(zxName)){
			sb.append(" where md.appName like '%" + zxName + "%' ");
		}
		
		
		sb.append(" group by id order by md.approver ");
		
		try{
			
			List<Stat1Bean> rs = this.getCurrSession().createSQLQuery(sb.toString()).setResultTransformer(Transformers.aliasToBean(Stat1Bean.class)).list();
			int count = rs.size();
			
			List<Stat1Bean> list = new ArrayList<Stat1Bean>();
			
			for(int i=0;i<rs.size();i++){
				if(i >= (pageNo -1) *pageSize &&
						i< pageNo*pageSize ){
					list.add(rs.get(i));
				}
			}
			
			page = new Page(list, pageNo, pageSize, count);
			
		}catch(Exception e){
			System.out.println(e.getStackTrace());
		}
		
		return page;
	}
	
	/**
	 * 获取当前年的申报项目实体
	 * @param catalog1 大类别
	 * @param approver 申报人
	 * @param appYear 年份
	 * @return 项目实体
	 */
	public Mdata getCurrentItem(String catalog1,String approver,String appYear)
	{
		String hql = "from Mdata where catalog1=? and approver=? and appYear=? and statusOrder='0100'";
		return this.findUnique(hql, new Object[]{catalog1,approver,appYear});
	}

}
